#!/usr/bin/perl

# PERL MODULES WE WILL BE USING
use DBI;
use DBD::mysql;

# ALLOCATE VARIABLE
sub new {
	my $self = {};
	bless $self;
	$self->{PLATFORM};
	$self->{DATABASE};
	$self->{HOST};
	$self->{PORT};
	$self->{TABLENAME};
	$self->{USER};
	$self->{PW};
	#warn "CREATING " . \$self;
	return bless $self;
}

# DESALLOCATE VARIABLES
sub DESTROY {
	my $self = shift;
	warn "DESTROYING $self";
}

# CONECT TO THE DB
sub ConnectDB {
	my $self = shift;
	$self->Reconfigure( $self, "mysql", shift, shift, "NoTable");

	#DATA SOURCE NAME
	$dsn = "dbi:"
	  . $self->{PLATFORM} . ":"
	  . $self->{DATABASE} . ":"
	  . $self->{HOST} . ":"
	  . $self->{PORT};
	print "dsn = " . $dsn . "\n";

	# PERL DBI CONNECT (RENAMED HANDLE)
	$dbdatachk = DBI->connect( $dsn, $self->{USER}, $self->{PW} )
	  or die "Unable to connect: $DBI::errstr\n";
}

# Conect to the DB using one table
sub ConnectDB {
	my $self = shift;
	$self->Reconfigure( $self, "mysql", shift, shift, shift);

	#DATA SOURCE NAME
	$dsn = "dbi:"
	  . $self->{PLATFORM} . ":"
	  . $self->{DATABASE} . ":"
	  . $self->{HOST} . ":"
	  . $self->{PORT};
#	print "dsn = " . $dsn . "\n";

	# PERL DBI CONNECT (RENAMED HANDLE)
	$dbdatachk = DBI->connect( $dsn, $self->{USER}, $self->{PW} )
	  or die "Unable to connect: $DBI::errstr\n";
}


# CONFIG VARIABLES
sub Reconfigure {
	my $self = shift;
	$a                 = shift;    #????????????
	$self->{PLATFORM}  = shift;
	$self->{DATABASE}  = shift;
	$self->{HOST}      = shift;
	$self->{TABLENAME} = shift;
	$self->{PORT}      = "3306";
	$self->{USER}      = "Datachk";
	$self->{PW}        = "Bt8f65K97eUX2R3X";
#print "Reconfig:".$self->{PLATFORM}.":".$self->{DATABASE}.":".$self->{HOST}.":".$self->{PORT}.":".$self->{USER}.":".$self->{PW}.":".$self->{TABLENAME}."\n";
}

# RECEIVE QUERYS
sub Query {
	my $self = shift;
	$self->{QUERY} = shift;

	# PREPARE THE QUERY STRING
	$query_handle = $dbdatachk->prepare( $self->{QUERY} );

	# EXECUTE THE QUERY
	$query_handle->execute();
	return $query_handle;
}

sub listtables{
	return $dbdatachk->tables();
}

sub name {
	my $self = shift;
	if (@_) { $self->{NAME} = shift }
	return $self->{NAME};
}

warn "\n\nStarting Program To Create the Data Base\n";

# Connect to the Data Base
my $a = new();
$a->ConnectDB( "Datachk", "localhost");

# Drop Old Data Base Datachk
@list = $a->listtables;
foreach $table (@list) {
	$b = $a->Query("DROP TABLE $table");
	print "$table Droped!\n";
}

# Create Tables
$b = $a->Query(qq(				
CREATE TABLE HD (
CodigoBarras VARCHAR(10) PRIMARY KEY,
Tamanho INT UNSIGNED,
Tipo VARCHAR(15)
);
				));

$b = $a->Query(qq(
CREATE TABLE Disquete (
CodigoBarras VARCHAR(10) PRIMARY KEY
);
				));

$b = $a->Query(qq(		
CREATE TABLE Processador (
CodigoBarras VARCHAR(10) PRIMARY KEY,
Tipo VARCHAR(20),
Velocidade INT UNSIGNED,
Socket VARCHAR(15)
);
					));
					
$b = $a->Query(qq(
CREATE TABLE Memoria (
CodigoBarras VARCHAR(10) PRIMARY KEY,
Tipo VARCHAR(10),
Tamanho INT UNSIGNED
);
					));
					
$b = $a->Query(qq(					
CREATE TABLE PlacaRede (
CodigoBarras VARCHAR(10) PRIMARY KEY,
Modelo VARCHAR(20),
TaxaTransmissao VARCHAR (20)
);
					));

$b = $a->Query(qq(
CREATE TABLE Usuario (
Matricula VARCHAR(11) PRIMARY KEY,
Nome VARCHAR(50) NOT NULL,
DataAdmissao DATE NOT NULL,
NivelPermissao TINYINT NOT NULL,
Login VARCHAR(10) NOT NULL UNIQUE,
Senha VARCHAR(12) NOT NULL
);
					));

$b = $a->Query(qq(
CREATE TABLE Gabinete (
CodigoBarras VARCHAR(10) PRIMARY KEY,
CACIC VARCHAR(13) UNIQUE,
DataRegistro DATETIME,
Tipo VARCHAR(25)
);
					));
$b = $a->Query(qq(
CREATE TABLE StatusGabinete (
Data DATETIME PRIMARY KEY,
Status VARCHAR(15),
CodigoBarras VARCHAR(10),
Matricula VARCHAR(11),
FOREIGN KEY(CodigoBarras) REFERENCES Gabinete (CodigoBarras),
FOREIGN KEY(Matricula) REFERENCES Usuario (Matricula)
);
					));
					
$b = $a->Query(qq(
CREATE TABLE PlacaMae (
CodigoBarras VARCHAR(10) PRIMARY KEY,
Modelo VARCHAR(10),
Socket VARCHAR(10),
QtdSlotsMemoria TINYINT UNSIGNED,
SomOnBoard TINYINT UNSIGNED,
PortaUSB TINYINT UNSIGNED,
InterfaceSCSI TINYINT UNSIGNED,
InterfaceSATA TINYINT UNSIGNED,
InterfaceIDE TINYINT UNSIGNED,
BarramentoAGP TINYINT UNSIGNED,
BarramentoISA TINYINT UNSIGNED,
BarramentoPCI TINYINT UNSIGNED
);
					));
					
$b = $a->Query(qq(
CREATE TABLE Fonte (
CodigoBarras VARCHAR(10) PRIMARY KEY,
Potencia INT UNSIGNED,
Tipo VARCHAR(10),
Conector VARCHAR(15),
QuantidadePinos TINYINT UNSIGNED
);
					));
					
$b = $a->Query(qq(
CREATE TABLE Monitor (
CodigoBarras VARCHAR(10) PRIMARY KEY,
CACIC VARCHAR(13) UNIQUE,
DataRegistro DATETIME,
Fabricante VARCHAR(20),
Tipo VARCHAR(10),
Polegadas TINYINT
);
					));
					
$b = $a->Query(qq(
CREATE TABLE Computador (
CodigoBarrasPC VARCHAR(10),
CodigoBarrasMonitor VARCHAR(10),
Teclado TINYINT,
Mouse TINYINT,
CaixaSom TINYINT,
Estabilizador TINYINT,
Lote VARCHAR(10),
PRIMARY KEY(CodigoBarrasPC,CodigoBarrasMonitor),
FOREIGN KEY(CodigoBarrasPC) REFERENCES Gabinete (CodigoBarras),
FOREIGN KEY(CodigoBarrasMonitor) REFERENCES Monitor (CodigoBarras)
);
					));
					
$b = $a->Query(qq(
CREATE TABLE KitTelecentro (
Lote VARCHAR(10) PRIMARY KEY,
Destino VARCHAR(50),
Data DATE
);
					));
					
$b = $a->Query(qq(
CREATE TABLE Doacao (
Data DATE,
TipoEquipamento VARCHAR(15),
Origem VARCHAR(50),
Quantidade INT UNSIGNED,
PRIMARY KEY(Data,TipoEquipamento,Origem)
);
					));
					
$b = $a->Query(qq(
CREATE TABLE Descarte (
Identificador VARCHAR(10) PRIMARY KEY,
Data DATETIME,
TipoEquipamento VARCHAR(15),
Origem VARCHAR(50),
Observacao VARCHAR(200),
Matricula VARCHAR(11),
FOREIGN KEY(Matricula) REFERENCES Usuario (Matricula)
);
					));
					
$b = $a->Query(qq(
CREATE TABLE Hub (
CodigoBarras VARCHAR(10) PRIMARY KEY,
DataRegistro DATETIME,
Lote VARCHAR(10),
FOREIGN KEY(Lote) REFERENCES KitTelecentro (Lote)
);
					));
					
$b = $a->Query(qq(
CREATE TABLE Notebook (
CodigoBarras VARCHAR(10) PRIMARY KEY,
CACIC VARCHAR(13) UNIQUE,
Configuracao VARCHAR(25),
Tipo VARCHAR(25),
DataRegistro DATETIME,
Lote VARCHAR(10),
FOREIGN KEY(Lote) REFERENCES KitTelecentro (Lote)
);
					));
					
$b = $a->Query(qq(
CREATE TABLE Impressora (
CodigoBarras VARCHAR(10) PRIMARY KEY,
CACIC VARCHAR(13) UNIQUE,
Fabricante VARCHAR(20),
Modelo VARCHAR(15),
Tipo VARCHAR(15),
DataRegistro DATETIME,
Lote VARCHAR(10),
FOREIGN KEY(Lote) REFERENCES KitTelecentro (Lote)
);
					));
					
$b = $a->Query(qq(
CREATE TABLE Scanner (
CodigoBarras VARCHAR(10) PRIMARY KEY,
Fabricante VARCHAR(20),
DataRegistro DATETIME,
Lote VARCHAR(10),
FOREIGN KEY(Lote) REFERENCES KitTelecentro (Lote)
);
					));
					
$b = $a->Query(qq(
CREATE TABLE Acessorios (
TipoEquipamento VARCHAR(15) PRIMARY KEY,
Quantidade INT UNSIGNED
);
					));
					
$b = $a->Query(qq(
CREATE TABLE Rabicho (
Tipo VARCHAR(15) PRIMARY KEY,
Quantidade INT UNSIGNED
);
					));
					
$b = $a->Query(qq(
CREATE TABLE Sucata (
TipoEquipamento VARCHAR(15) PRIMARY KEY,
Quantidade INT UNSIGNED
);
					));
					
$b = $a->Query(qq(
CREATE TABLE PlacaVideo (
CodigoBarras VARCHAR(10) PRIMARY KEY,
Memoria INT UNSIGNED,
Processador INT UNSIGNED,
Aceleradora3D TINYINT UNSIGNED
);
					));
					
$b = $a->Query(qq(
CREATE TABLE Entrega (
CodigoBarras VARCHAR(10),
DataEntrega DATETIME,
Devolucao TINYINT NOT NULL,
MatriculaEstoque VARCHAR(11) NOT NULL,
MatriculaManutencao VARCHAR(11) NOT NULL,
PRIMARY KEY(CodigoBarras,DataEntrega),
FOREIGN KEY(MatriculaEstoque) REFERENCES Usuario (Matricula),
FOREIGN KEY(MatriculaManutencao) REFERENCES Usuario (Matricula)
);
					));
					
$b = $a->Query(qq(
CREATE TABLE DriverOptico (
CodigoBarras VARCHAR(10) PRIMARY KEY,
Velocidade INT UNSIGNED,
Tipo VARCHAR(15)
);
					));
					
$b = $a->Query(qq(
CREATE TABLE Pecas (
CodigoBarras VARCHAR(10) PRIMARY KEY,
Testado TINYINT,
Fabricante VARCHAR(20),
DataRegistro DATETIME,
Origem VARCHAR(50),
CodigoBarrasPC VARCHAR(10),
FOREIGN KEY(CodigoBarrasPC) REFERENCES Gabinete (CodigoBarras)
);
					));

$b = $a->Query(qq(
CREATE TABLE StatusPecas (
Data DATETIME PRIMARY KEY,
Status VARCHAR(15),
CodigoBarras VARCHAR(10),
Matricula VARCHAR(11),
FOREIGN KEY(CodigoBarras) REFERENCES Pecas (CodigoBarras),
FOREIGN KEY(Matricula) REFERENCES Usuario (Matricula)
);
					));
					
$b = $a->Query("ALTER TABLE HD ADD FOREIGN KEY(CodigoBarras) REFERENCES Pecas (CodigoBarras);");
$b = $a->Query("ALTER TABLE Disquete ADD FOREIGN KEY(CodigoBarras) REFERENCES Pecas (CodigoBarras);");
$b = $a->Query("ALTER TABLE Memoria ADD FOREIGN KEY(CodigoBarras) REFERENCES Pecas (CodigoBarras);");
$b = $a->Query("ALTER TABLE Memoria ADD FOREIGN KEY(CodigoBarras) REFERENCES Pecas (CodigoBarras);");
$b = $a->Query("ALTER TABLE PlacaRede ADD FOREIGN KEY(CodigoBarras) REFERENCES Pecas (CodigoBarras);");
$b = $a->Query("ALTER TABLE PlacaMae ADD FOREIGN KEY(CodigoBarras) REFERENCES Pecas (CodigoBarras);");
$b = $a->Query("ALTER TABLE Fonte ADD FOREIGN KEY(CodigoBarras) REFERENCES Pecas (CodigoBarras);");
$b = $a->Query("ALTER TABLE Computador ADD FOREIGN KEY(Lote) REFERENCES KitTelecentro (Lote);");
$b = $a->Query("ALTER TABLE PlacaVideo ADD FOREIGN KEY(CodigoBarras) REFERENCES Pecas (CodigoBarras);");
$b = $a->Query("ALTER TABLE Entrega ADD FOREIGN KEY(CodigoBarras) REFERENCES Pecas (CodigoBarras);");
$b = $a->Query("ALTER TABLE DriverOptico ADD FOREIGN KEY(CodigoBarras) REFERENCES Pecas (CodigoBarras);");


	  warn "\nJust Reseted the Datachk DataBase\n";
	  exit;
